from code.data.Answer import Answer
from code.dao.user_management import __check_user_exist
from code.dao.homework_management import __check_homework_exit
from code.dao.config import mysql_conn, cursor

import logging
import pymysql
import traceback
import time
import datetime


def add_answer(answer: Answer):
    if answer.userID is None:
        logging.error("UserID is required.")
        return False
    if not __check_user_exist(answer.userID):
        return False
    if answer.homeworkID is None:
        logging.error("HomeworkID is required.")
        return False
    if not __check_homework_exit(answer.homeworkID):
        return False
    sql = "INSERT INTO answer(status, userID, homeworkID) VALUES (0, '{}', {})".format(
        answer.userID, answer.homeworkID)
    try:
        cursor.execute(sql)
        mysql_conn.commit()
    except pymysql.Error as e:
        logging.error("Inserting answer into database failed: " + str(e.args[1]))
        mysql_conn.rollback()
        return False
    logging.info("Answer is added with SQL: " + sql)
    return True


def search_answer(answer: Answer, search_by="all"):
    answers = []
    if search_by == "all":
        sql = "SELECT * FROM answer"
    elif search_by == "answerID":
        if answer.answerID is None:
            logging.error("AnswerID is required.")
            return None
        sql = "SELECT * FROM answer WHERE answerID = '{}'".format(answer.answerID)
    elif search_by == "status":
        if answer.status is None:
            logging.error("Status is required.")
            return None
        sql = "SELECT * FROM answer WHERE status = {}".format(answer.status)
    elif search_by == "userID":
        if answer.userID is None:
            logging.error("UserID is required.")
            return None
        sql = "SELECT * FROM answer WHERE userID = '{}'".format(answer.userID)
    else:
        logging.error("Attribute for search_by is not exist, use one in "
                      '["all", "answerID", "status", "userID"]')
        return None
    try:
        cursor.execute(sql)
        results = cursor.fetchall()
        if not results:
            logging.warning("Answer is not exist.")
            return None
    except pymysql.Error as e:
        logging.error("Searching for answer failed: " + str(e.args[1]))
        traceback.print_exc()
        return None
    for result in results:
        answers.append(Answer(result[0], result[1], result[2], result[3], result[4], result[5], \
                              result[6], result[7], result[8], result[9], result[10], result[11]))
    logging.info("Search answer with SQL: {}".format(sql))
    return answers


def search_answer_by_homeworkID(answer: Answer, search_by="all"):
    if answer.homeworkID is None:
        logging.error("HomeworkID is required.")
        return None
    if not __check_homework_exit(answer.homeworkID):
        return None
    answers = []
    if search_by == "all":
        sql = "SELECT * FROM answer WHERE homeworkID = {}".format(answer.homeworkID)
    elif search_by == "answerID":
        if answer.answerID is None:
            logging.error("AnswerID is required.")
            return None
        sql = "SELECT * FROM answer WHERE answerID = {} AND homeworkID = {}".format(answer.answerID, answer.homeworkID)
    elif search_by == "status":
        if answer.status is None:
            logging.error("Status is required.")
            return None
        sql = "SELECT * FROM answer WHERE status = {} AND homeworkID = {}".format(answer.status, answer.homeworkID)
    elif search_by == "userID":
        if answer.userID is None:
            logging.error("UserID is required.")
            return None
        sql = "SELECT * FROM answer WHERE userID = '{}' AND homeworkID = {}".format(answer.userID, answer.homeworkID)
    else:
        logging.error("Attribute for search_by is not exist, use one in "
                      '["all", "answerID", "status", "userID"]')
        return None
    try:
        cursor.execute(sql)
        results = cursor.fetchall()
        if not results:
            logging.warning("Answer is not exist.")
            return None
    except pymysql.Error as e:
        logging.error("Searching for answer failed: " + str(e.args[1]))
        traceback.print_exc()
        return None
    for result in results:
        answers.append(Answer(result[0], result[1], result[2], result[3], result[4], result[5],\
                              result[6], result[7], result[8], result[9], result[10], result[11]))
    logging.info("Search answer with SQL: {}".format(sql))
    return answers


def update_answer(answer: Answer, update_by: str):
    if answer.answerID is None:
        logging.error("AnswerID is required.")
        return False
    if not __check_answer_exist(answer.answerID):
        return False
    if update_by == "answer":
        if answer.answer_content is None:
            answer_content = "NULL"
        else:
            answer_content = "'" + answer.answer_content + "'"
        if answer.answer_file_path is None:
            answer_file_path = "NULL"
        else:
            answer_file_path = "'" + answer.answer_file_path.replace("\\", "\\\\") + "'"
        if answer.answer_file_size is None:
            answer_file_size = "NULL"
        else:
            answer_file_size = answer.answer_file_size
        sql = "UPDATE answer SET answer_content = {}, answer_file_path = {}, answer_file_size = {}, status = {}, " \
              "answer_time = CURRENT_TIMESTAMP ".\
            format(answer_content, answer_file_path, answer_file_size, answer.status) +\
            "WHERE answerID = {}".format(answer.answerID)
    elif update_by == "review":
        if answer.review_content is None:
            review_content = "NULL"
        else:
            review_content = "'" + answer.review_content + "'"
        if answer.review_file_path is None:
            review_file_path = "NULL"
        else:
            review_file_path = "'" + answer.review_file_path.replace("\\", "\\\\") + "'"
        if answer.review_file_size is None:
            review_file_size = "NULL"
        else:
            review_file_size = answer.review_file_size
        sql = "UPDATE answer SET review_content = {}, review_file_path = {}, review_file_size = {}, status = {}, " \
              "review_time = CURRENT_TIMESTAMP ".\
            format(review_content, review_file_path, review_file_size, answer.status) +\
            "WHERE answerID = {}".format(answer.answerID)
    else:
        logging.error("Attribute for update_by is not exist, use one in "
                      '["answer", "review"]')
        return False
    try:
        cursor.execute(sql)
        mysql_conn.commit()
    except pymysql.Error as e:
        logging.error("Update answer from database failed: " + str(e.args[1]))
        mysql_conn.rollback()
        return False
    logging.info("Answer is updated with SQL: {}".format(sql))
    return True


def delete_answer(answer: Answer, detele_by="answerID"):
    if detele_by == "answerID":
        if answer.answerID is None:
            logging.error("AnswerID is required.")
            return False
        if not __check_answer_exist(answer.answerID):
            return False
        sql = "DELETE FROM answer WHERE answerID = {}".format(answer.answerID)
    elif detele_by == "userID":
        if answer.userID is None:
            logging.error("UserID is required.")
            return False
        if not __check_user_exist(answer.userID):
            return False
        sql = "DELETE FROM answer WHERE userID = '{}'".format(answer.userID)
    elif detele_by == "homeworkID":
        if answer.homeworkID is None:
            logging.error("HomeworkID is required.")
            return False
        if not __check_homework_exit(answer.homeworkID):
            return False
        sql = "DELETE FROM answer WHERE homeworkID = {}".format(answer.homeworkID)
    try:
        cursor.execute(sql)
        mysql_conn.commit()
    except pymysql.Error as e:
        logging.error("Delete answer from database failed: " + str(e.args[1]))
        mysql_conn.rollback()
        return False
    logging.info("Answer is deleted with SQL: {}".format(sql))
    return True


def __check_answer_exist(answerID: int):
    sql = "SELECT * FROM answer WHERE answerID = {}".format(answerID)
    try:
        cursor.execute(sql)
        results = cursor.fetchall()
        if not results:
            logging.error("Answer {} is not exist".format(answerID))
            return False
    except pymysql.Error as e:
        logging.error("Searching for answer failed: " + str(e.args[1]))
        traceback.print_exc()
        return False
    return True


if __name__ == "__main__":
    answer1 = Answer(userID="admin", homeworkID=2)
    print(add_answer(answer1))
    time.sleep(1)
    results1 = search_answer_by_homeworkID(answer1, search_by="userID")
    for result in results1:
        print(result.to_string())
        result.answer_content = "This is a test for answer"
        result.answer_file_path = "D:\\test_for_answer.file"
        result.answer_file_size = "256"
        result.status = 1
        print(update_answer(result, update_by="answer"))
    time.sleep(1)
    results2 = search_answer_by_homeworkID(answer1, search_by="userID")
    for result in results2:
        print(result.to_string())
        result.review_content = "This is a test for review"
        result.review_file_path = "D:\\test_for_review.file"
        result.review_file_size = "1024"
        result.status = 2
        print(update_answer(result, update_by="review"))
    results3 = search_answer_by_homeworkID(answer1, search_by="userID")
    for result in results3:
        print(result.to_string())
    print(delete_answer(answer1,detele_by="userID"))
